<?php
/**
 * 房源数据城市月统计表
 */

namespace app\crontab\controller\always;


use custom\Log;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;

class HouseStatisticsCityMonth extends Command {


    protected function configure() {
        $this->setName('HouseStatisticsCityMonth');
    }

    protected function execute(Input $input, Output $output) {
        $this->initial();
    }

    public function initial() {
        // 关闭日志写入
        \think\facade\Log::close();
        echo "starting\n";
        date_default_timezone_set('PRC');
        $citys = config('global.house_city_all');
        $citys = join(',', $citys);
        $start_time = strtotime(date('Y-m', strtotime('-1 month')) . '-01');
        $end_time = strtotime(date('Y-m', time()) . '-01');
        //成交时间
        $where1 = "and erp_house_auction.deal_time >= $start_time and erp_house_auction.deal_time < $end_time";
        //拍卖时间
        $where2 = "and erp_house_auction.start_time < $end_time and erp_house_auction.end_time >= $start_time ";
        //上个月进行中总套数
        $jinxing = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') $where2 and erp_house_auction.auction_status = 2  ) as jinxing";
        //上个月待开始总套数
        $daikaishi = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') $where2  and erp_house_auction.auction_status = 1 ) as daikaishi";
        //上个月成交总套数
        $deal_total_number = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 $where1 ) as deal_total_number";
        //成交金额
        $deal_money = "(select sum(erp_house_auction.final_price) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 $where1) as deal_money";
        //竞价人数
        $bidders_number = "(select sum(erp_house_auction.enroll_num) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') $where2 ) as bidders_number";
        //上上月成交总套数
        $before_deal_total_number = "(select deal_total_number from erp_house_statistics_city_month where city_code = erp_region.city_code order by id desc limit 1 ) as before_deal_total_number";
        //上上月成交率
        $before_deal_rate = "(select deal_rate from erp_house_statistics_city_month where city_code = erp_region.city_code order by id desc limit 1 ) as before_deal_rate";
        //上上月成交金额
        $before_deal_money = "(select deal_money from erp_house_statistics_city_month where city_code = erp_region.city_code order by id desc limit 1 ) as before_deal_money";
        //上上月竞价人数
        $before_bidders_number = "(select bidders_number from erp_house_statistics_city_month where city_code = erp_region.city_code order by id desc limit 1 ) as before_bidders_number";
        //成交价100万以下的成交量（不包括100万）
        $money_number_100 = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and  erp_house_auction.final_price < 100000000 $where1 ) as money_number_100";
        //交价100-300万的成交量（包括100，不包括300万）
        $money_number_300 = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and  erp_house_auction.final_price >= 100000000 and erp_house_auction.final_price < 300000000 $where1) as money_number_300";
        //成交价300-500万的成交量（包括300，不包括500万）
        $money_number_500 = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and  erp_house_auction.final_price >= 300000000 and erp_house_auction.final_price < 500000000 $where1) as money_number_500";
        //成交价500万及以上的成交量（包括500）
        $money_number = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and  erp_house_auction.final_price >= 500000000 $where1) as money_number";
        //面积90平米以下的成交量（不包括90）
        $area_number_90 = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and erp_house.build_area < 900000 $where1) as area_number_90";
        //面积90-140平米的成交量（包括90，不包括140万）
        $area_number_140 = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and erp_house.build_area >= 900000 and erp_house.build_area < 1400000 $where1) as area_number_140";
        //面积140-210平米的成交量（包括140，不包括210万）
        $area_number_210 = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and erp_house.build_area >= 1400000 and erp_house.build_area < 2100000 $where1) as area_number_210";
        //面积210平米及以上的成交量（包括210）
        $area_number = "(select count(erp_house.id) from erp_house join erp_house_auction on erp_house_auction.id = erp_house.auction_id where erp_house.city_codes like concat(erp_region.p_codes,erp_region.city_code,'%') and erp_house_auction.auction_status = 3 and erp_house.build_area >= 2100000 $where1) as area_number";
        $data = Db::query("select city_code,name,{$jinxing},{$daikaishi},{$deal_total_number},{$deal_money},{$bidders_number},{$before_deal_total_number},{$before_deal_rate},{$before_deal_money},{$before_bidders_number},{$money_number_100},{$money_number_300},{$money_number_500},{$money_number},{$area_number_90},{$area_number_140},{$area_number_210},{$area_number} from erp_region where city_code in ({$citys})");
        foreach ($data as $k2 => $v2) {
            $jinxing_ = $v2['jinxing'] ?? 0;//进行中
            $daikaishi_ = $v2['daikaishi'] ?? 0;//待开始
            $deal_total_number = $v2['deal_total_number'] ?? 0;//成交量

            //总套数
            $total_number = $jinxing_ + $daikaishi_ + $deal_total_number;

            //成交率
            $_deal_rate = empty($total_number) ? 0 : ($deal_total_number / $total_number) * 100;
            $deal_rate = (int)$_deal_rate;

            $deal_money = $v2['deal_money'] ?? 0;

            $bidders_number = $v2['bidders_number'] ?? 0;

            if (empty($v2['before_deal_total_number']) && !empty($deal_total_number)) {
                $deal_number_ratio = 100;
            } elseif (empty($v2['before_deal_total_number']) && empty($deal_total_number)) {
                $deal_number_ratio = 0;
            } else {
                $_deal_number_ratio = round(($deal_total_number - $v2['before_deal_total_number']) / $v2['before_deal_total_number'], 2);
                $deal_number_ratio = (int)($_deal_number_ratio * 100);
            }

            if (empty($v2['before_deal_rate']) && !empty($deal_rate)) {
                $deal_rate_ratio = 100;
            } elseif (empty($v2['before_deal_rate']) && empty($deal_rate)) {
                $deal_rate_ratio = 0;
            } else {
                $_deal_rate_ratio = round(($deal_rate - $v2['before_deal_rate']) / $v2['before_deal_rate'], 2);
                $deal_rate_ratio = (int)($_deal_rate_ratio * 100);
            }

            if (empty($v2['before_deal_money']) && !empty($deal_money)) {
                $deal_money_ratio = 100;
            } elseif (empty($v2['before_deal_money']) && empty($deal_money)) {
                $deal_money_ratio = 0;
            } else {
                $_deal_money_ratio = round(($deal_money - $v2['before_deal_money']) / $v2['before_deal_money'], 2);
                $deal_money_ratio = (int)($_deal_money_ratio * 100);
            }

            if (empty($v2['before_bidders_number']) && empty($bidders_number)) {
                $bidders_number_ratio = 0;
            } elseif (empty($v2['before_bidders_number']) && !empty($bidders_number)) {
                $bidders_number_ratio = 100;
            } else {
                $_bidders_number_ratio = round(($bidders_number - $v2['before_bidders_number']) / $v2['before_bidders_number'], 2);
                $bidders_number_ratio = (int)($_bidders_number_ratio * 100);
            }

            $insert = [
                'city_code'            => $v2['city_code'],    //城市code
                'total_number'         => $total_number,  //总套数
                'deal_total_number'    => $deal_total_number,//成交总套数
                'deal_rate'            => $deal_rate, //成交率
                'deal_money'           => $deal_money,//成交金额（单位分）
                'bidders_number'       => $bidders_number,//竞价人数
                'deal_number_ratio'    => $deal_number_ratio,//成交量的环比（最多两位小数）
                'deal_rate_ratio'      => $deal_rate_ratio,//成交率的环比（最多两位小数）
                'deal_money_ratio'     => $deal_money_ratio,//成交金额环比（最多两位小数）
                'bidders_number_ratio' => $bidders_number_ratio,//竞价人数环比（最多两位小数）
                'money_number_100'     => $v2['money_number_100'] ?? 0,//成交价100万以下的成交量（不包括100万）
                'money_number_300'     => $v2['money_number_300'] ?? 0,//成交价100-300万的成交量（包括100，不包括300万）
                'money_number_500'     => $v2['money_number_500'] ?? 0,//成交价300-500万的成交量（包括300，不包括500万）
                'money_number'         => $v2['money_number'] ?? 0,//成交价500万及以上的成交量（包括500）
                'area_number_90'       => $v2['area_number_90'] ?? 0,//面积90平米以下的成交量（不包括90）
                'area_number_140'      => $v2['area_number_140'] ?? 0,//面积90-140平米的成交量（包括90，不包括140万）
                'area_number_210'      => $v2['area_number_210'] ?? 0,//面积140-210平米的成交量（包括140，不包括210万）
                'area_number'          => $v2['area_number'] ?? 0,//面积210平米及以上的成交量（包括210）
                'info_url'             => '',//详细数据url
                'count_time'           => strtotime(date('Y-m', strtotime('-1 month')) . '-01'),
            ];
            $res[] = $insert;
        }
        $total = count($res);
        if (Db::name('house_statistics_city_month')
            ->where('count_time', strtotime(date('Y-m', strtotime('-1 month')) . '-01'))
            ->select()) {
            Log::write("上个月已经统计过了", 'house_statistics_city_month.log');
            die;
        }
        $suc = Db::name('house_statistics_city_month')->data($res)->limit(100)->insertAll();
        echo 'success->' . $suc . PHP_EOL;
        echo 'total->' . $total . PHP_EOL;
        echo "end" . PHP_EOL;
        $res = json_encode($res);
        Log::write("success->$suc;total->$total;res:$res", 'house_statistics_city_month.log');
    }


}